package Gr

import (
	"encoding/json"
	"errors"
	"fmt"
	"db2s/ETL"
	ea "db2s/encryptionAlgorithm"
	"db2s/global"
	"math"
	"reflect"
	"runtime"
	"sort"
	"strconv"
	"strings"
)

type TableInfoMeta struct {
}

func (cs TableInfoMeta) AvgRowLength(s global.TablesMetaInfoInput) (res global.AvgRowLengthReturnResult, err error) {
	var (
		event = "[clusterAvgRowLength]"
		f1    any
	)
	res.BaseResult.Sql = fmt.Sprintf("select `AVG_ROW_LENGTH` as `AvgRowLength` from information_schema.tables where table_schema='%v' and table_name ='%v'", s.TableInfo.Schema, s.TableInfo.Table)
	if res.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: res.BaseResult.Sql,
		Expect:       ETL.Map,
	}); err != nil || f1 == nil {
		if err != nil {
			err = errors.New(fmt.Sprintf("%v %v", event, err))
		}
		return
	}
	if l, ok := f1.(map[string]any)["AvgRowLength"]; ok {
		if res.Result, err = strconv.ParseInt(fmt.Sprintf("%v", l), 10, 64); err != nil {
			err = errors.New(fmt.Sprintf("%v %v", event, err))
			return
		}
	}
	return
}

/*
DataSize 查询表的统计信息，获取表的数据大小，数据大小+索引大小，单位MB
*/
func (cs TableInfoMeta) DataSize(s global.TablesMetaInfoInput) (result global.DataSizeReturnResult, err error) {
	var (
		event = "[clusterDataSize]"
		f1    any
	)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"]}
	result.Sql = fmt.Sprintf("SELECT ROUND(SUM(DATA_LENGTH) / 1048576, 2) AS `dataSizeMB`,ROUND(SUM(INDEX_LENGTH) / 1048576, 2) AS `indexSizeMB` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%v' AND TABLE_NAME = '%v'", s.TableInfo.Schema, s.TableInfo.BackendTableName)
	if len(s.TableInfo.Schema) == 0 || len(s.TableInfo.BackendTableName) == 0 || len(s.DB) == 0 || active.DB == nil {
		err = errors.New(fmt.Sprintf("%v sql general fail. schema or table is entry. Execution process:{schema:%v,table:%v,sql:%v,shard:%v,db:%v,err:%v}", event, s.TableInfo.Schema, s.TableInfo.BackendTableName, result.Sql, s.TableInfo.ShardName, s.DB, err))
		return
	}
	if result.ExecTimeout, f1, err = active.SqlQuery(result.Sql, ETL.Map); err != nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,shard:%v,db:%v,err:%v}", event, result.Sql, "single", s.DB, err))
		return
	}
	if err = resultTypeCheck(event, ETL.Map, f1); err != nil || f1 == nil {
		if err == nil {
			err = errors.New(fmt.Sprintf("%v result is empty. Execution process:{sql:%v,shard:%v,db:%v,result:%v}", event, result.Sql, "single", s.DB, f1))
		}
		return
	}
	for k, v := range []string{"dataSizeMB", "indexSizeMB"} {
		if _, ok := f1.(map[string]interface{})[v]; !ok {
			err = errors.New(fmt.Sprintf("%v query result not include %v. process:{sql:%v,result:%v}", v, event, result.Sql, f1))
			return
		}
		v1 := fmt.Sprintf("%v", f1.(map[string]interface{})[v])
		if strings.HasPrefix(v1, ".") {
			v1 = fmt.Sprintf("0%v", v1)
		}
		if !isNumeric(v1) {
			err = errors.New(fmt.Sprintf("%v The result contains illegal non-numeric data. process:{sql:%v,result:%v}", event, result.Sql, v1))
			return
		}
		var v2 float64
		if v2, err = strconv.ParseFloat(v1, 64); err != nil {
			err = errors.New(fmt.Sprintf("%v string strconv float64 fail, strconv data is {%v} error is {%v}", event, v1, err))
			return
		}
		switch k {
		case 0:
			result.DataSizeMB = math.Round(v2*100) / 100
		case 1:
			result.IndexSizeMB = math.Round(v2*100) / 100
		}
	}
	return
}

// Comment 获取表的comment信息
func (cs TableInfoMeta) Comment(s global.TablesMetaInfoInput) (result global.TableCommentReturnResult, err error) {
	var (
		event = "[MySQLComment]"
		f1    any
	)
	strSql := fmt.Sprintf("SELECT REGEXP_SUBSTR(TABLE_COLLATION, '^[^_]+') AS `character`,TABLE_COLLATION as `collation`,TABLE_COMMENT as `comment` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%v' AND TABLE_NAME='%v'", s.TableInfo.Schema, s.TableInfo.Table)
	if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: strSql,
		Expect:       ETL.Map,
	}); err != nil || f1 == nil {
		if err != nil {
			err = errors.New(fmt.Sprintf("%v %v", event, err))
		}
		return
	}
	if v, ok := f1.(map[string]any)["comment"]; ok {
		switch {
		case strings.EqualFold(fmt.Sprintf("%v", v), "<null>"), strings.EqualFold(fmt.Sprintf("%v", v), "<entry>"), len(fmt.Sprintf("%v", v)) == 0:
			result.Result.Comment = "<entry>"
		default:
			result.Result.Comment = fmt.Sprintf("%v", v)
		}
	}
	if v, ok := f1.(map[string]any)["collation"]; ok {
		switch {
		case strings.EqualFold(fmt.Sprintf("%v", v), "<null>"), strings.EqualFold(fmt.Sprintf("%v", v), "<entry>"), len(fmt.Sprintf("%v", v)) == 0:
			result.Result.Collate = "<entry>"
		default:
			result.Result.Collate = fmt.Sprintf("%v", v)
		}
	}
	if v, ok := f1.(map[string]any)["character"]; ok {
		switch {
		case strings.EqualFold(fmt.Sprintf("%v", v), "<null>"), strings.EqualFold(fmt.Sprintf("%v", v), "<entry>"), len(fmt.Sprintf("%v", v)) == 0:
			result.Result.Collate = "<entry>"
		default:
			result.Result.Character = fmt.Sprintf("%v", v)
		}
	}
	return
}

// IfTableLob 判断表是否存在lob字段
func (cs TableInfoMeta) IfTableLob(s global.TablesMetaInfoInput) (result global.TableBigVarcharMeta, err error) {
	var (
		event                  = "[clusterIfTableLob]"
		res                    any
		columnName, columnType []string
	)
	result.BaseResult.Sql = fmt.Sprintf("SELECT `COLUMN_NAME` as `columnName`, `DATA_TYPE` as `columnType` FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '%v'  AND TABLE_NAME = '%v' AND DATA_TYPE IN ('tinyblob', 'blob', 'mediumblob', 'longblob')", s.TableInfo.Schema, s.TableInfo.BackendTableName)
	if _, res, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: result.BaseResult.Sql,
		Expect:       ETL.SMap,
	}); err != nil || res == nil {
		if err != nil {
			err = errors.New(fmt.Sprintf("%v %v", event, err))
		}
		return
	}
	for _, v := range res.([]map[string]any) {
		columnName = append(columnName, fmt.Sprintf("%v", v["columnName"]))
		columnType = append(columnType, fmt.Sprintf("%v", v["columnType"]))
	}
	if len(columnName) > 0 && len(columnType) > 0 {
		result = global.TableBigVarcharMeta{
			ColumnName: columnName,
			ColumnType: columnType,
		}
	}
	return
}

// IfBigVarchar 判断表是否存在varchar>1000
func (cs TableInfoMeta) IfBigVarchar(s global.TablesMetaInfoInput) (result global.TableBigVarcharMeta, err error) {
	var (
		event                  = "[clusterIfBigVarchar]"
		res                    any
		columnName, columnType []string
	)
	result.BaseResult.Sql = fmt.Sprintf("SELECT `COLUMN_NAME` as `columnName`,`DATA_TYPE` as `columnType`, `CHARACTER_MAXIMUM_LENGTH` as `dataLength` FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '%v' AND TABLE_NAME = '%v' AND DATA_TYPE in ('varchar','text','mediumtext','longtext')  AND CHARACTER_MAXIMUM_LENGTH > %d", s.TableInfo.Schema, s.TableInfo.BackendTableName, 1000)
	if result.BaseResult.TimeOut, res, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: result.BaseResult.Sql,
		Expect:       ETL.SMap,
	}); err != nil || res == nil {
		if err != nil {
			err = errors.New(fmt.Sprintf("%v %v", event, err))
		}
		return
	}
	for _, v := range res.([]map[string]any) {
		columnName = append(columnName, fmt.Sprintf("%v", v["columnName"]))
		columnType = append(columnType, fmt.Sprintf("%v", v["columnType"]))
	}
	if len(columnName) > 0 && len(columnType) > 0 {
		result = global.TableBigVarcharMeta{
			ColumnName: columnName,
			ColumnType: columnType,
		}
	}
	return
}

func (cs TableInfoMeta) DropPartitionSql(s global.TablesMetaInfoInput) (any, error) {
	var alterGather []string
	for k := range s.PartitionMetaGarth.DstMeta {
		alterGather = append(alterGather, fmt.Sprintf("alter table `%v`.`%v` drop partition `%v`;", s.TableInfo.Schema, s.TableInfo.Table, k))
	}
	return global.AlterSqlResult{
		Sql: strings.Join(alterGather, "\n"),
	}, nil
}
func (cs TableInfoMeta) AddPartitionSql(s global.TablesMetaInfoInput) (any, error) {
	var alterGather []string
	for _, v := range s.PartitionMetaGarth.SrcMeta {
		switch v.Meth {
		case "RANGE":
			alterGather = append(alterGather, fmt.Sprintf("alter table `%v`.`%v` add partition (partition `%v` VALUES LESS THAN (%v));", s.TableInfo.Schema, s.TableInfo.Table, v.Name, v.Rules))
		case "LIST":
			alterGather = append(alterGather, fmt.Sprintf("alter table `%v`.`%v` add partition (partition `%v` values in (%v));", s.TableInfo.Schema, s.TableInfo.Table, v.Name, v.Rules))
		}
	}
	return global.AlterSqlResult{
		Sql: strings.Join(alterGather, "\n"),
	}, nil
}
func (cs TableInfoMeta) ModifyPartitionSql(s global.TablesMetaInfoInput) (any, error) {
	var alterGather []string
	for k := range s.PartitionMetaGarth.DstMeta {
		alterGather = append(alterGather, fmt.Sprintf("alter table `%v`.`%v` drop partition `%v`;", s.TableInfo.Schema, s.TableInfo.Table, k))
	}
	for _, v := range s.PartitionMetaGarth.SrcMeta {
		switch v.Meth {
		case "RANGE":
			alterGather = append(alterGather, fmt.Sprintf("alter table `%v`.`%v` add partition (partition `%v` VALUES LESS THAN (%v));", s.TableInfo.Schema, s.TableInfo.Table, v.Name, v.Rules))
		case "LIST":
			alterGather = append(alterGather, fmt.Sprintf("alter table `%v`.`%v` add partition (partition `%v` values in (%v));", s.TableInfo.Schema, s.TableInfo.Table, v.Name, v.Rules))
		}
	}
	return global.AlterSqlResult{
		Sql: strings.Join(alterGather, "\n"),
	}, nil
}

func (cs TableInfoMeta) indexNameColumnMerge(s global.TablesMetaInfoInput) global.IndexMerge {
	var indexName = make(map[string][]global.IndexColumn)
	var indexType = make(map[string]string)
	var indexColumnSeq = make(map[string][]string)
	var indexNameOrderBy []string
	for _, v := range s.IndexColumn {
		if _, ok := indexType[v.IndexName]; !ok {
			var tmpIndexType string
			switch v.ColumnKey {
			case "P":
				tmpIndexType = "pri"
				v.IndexName = "PRIMARY"
			case "U":
				tmpIndexType = "uni"
			case "M":
				tmpIndexType = "mul"
			}
			indexType[v.IndexName] = tmpIndexType
			indexNameOrderBy = append(indexNameOrderBy, v.IndexName)
		}
		if v1, ok := indexName[v.IndexName]; ok {
			indexName[v.IndexName] = append(v1, v)
		} else {
			indexName[v.IndexName] = []global.IndexColumn{v}
		}
		if v1, ok := indexColumnSeq[v.IndexName]; ok {
			indexColumnSeq[v.IndexName] = append(v1, v.ColumnSeq)
		} else {
			indexColumnSeq[v.IndexName] = []string{v.ColumnSeq}
		}
	}
	return global.IndexMerge{
		Schema:           s.TableInfo.Schema,
		Table:            s.TableInfo.Table,
		IndexColumn:      indexName,
		IndexType:        indexType,
		OrderByIndexName: indexNameOrderBy,
		IndexColumnSeq:   indexColumnSeq,
	}
}

func (cs TableInfoMeta) IndexMerge(s global.TablesMetaInfoInput) global.IndexMerge {
	if len(s.IndexColumn) == 0 {
		return global.IndexMerge{}
	}
	//索引列处理，联合索引进行列合并
	//去除主键索引列、唯一索引列、普通索引列的所有列明
	p := cs.indexNameColumnMerge(s)
	return p
}
func (cs TableInfoMeta) IndexOptimizerTrace(s global.TablesMetaInfoInput) (any, error) {
	var (
		result global.IndexColumnSumGroup
		err    error
	)
	if len(s.IndexColumn) == 0 {
		return nil, nil
	}
	//索引列处理，联合索引进行列合并
	//去除主键索引列、唯一索引列、普通索引列的所有列明
	f1 := cs.IndexMerge(s)
	if global.IsEmptyStruct(f1) {
		//return global.IndexSelection{}
	}
	if result, err = cs.costChoice(f1); err != nil {
		return nil, err
	}
	return result, nil
}

// 一个索引的包含的列类型对比
func (cs TableInfoMeta) typeSelection(tsl []global.IndexColumn) []int {
	var typeDefinition = map[string]int{"int": 1, "time": 2, "string": 3, "text": 4}
	var intType = []string{"FLOAT", "DOUBLE", "DECIMAL", "NUMERIC"}
	var timeType = []string{"DATE", "TIME", "DATETIME", "TIMESTAMP", "YEAR"}
	var stringType = []string{"CHAR", "VARCHAR"}
	var typeSeq []int
	for _, v := range tsl {
		var exist = false
		if strings.HasSuffix(strings.ToUpper(v.ColumnType), "INT") {
			typeSeq = append(typeSeq, typeDefinition["int"])
			exist = true
		}
		for _, v1 := range intType {
			if strings.HasPrefix(strings.ToUpper(v.ColumnType), v1) {
				typeSeq = append(typeSeq, typeDefinition["int"])
				exist = true
			}
		}
		for _, v1 := range timeType {
			if strings.HasPrefix(strings.ToUpper(v.ColumnType), v1) {
				typeSeq = append(typeSeq, typeDefinition["time"])
				exist = true
			}
		}
		for _, v1 := range stringType {
			if strings.HasPrefix(strings.ToUpper(v.ColumnType), v1) {
				typeSeq = append(typeSeq, typeDefinition["string"])
				exist = true
			}
		}
		if !exist {
			typeSeq = append(typeSeq, 100)
		}
	}
	return typeSeq
}

// 多个索引的数据类型比较，按照规则选择一个或多个相同的
func (cs TableInfoMeta) typeSum(ll map[string][]int) (iName string) {
	var (
		iSlice []int
		iValue = 10000
	)
	for k, v := range ll {
		var iSum int
		for _, v1 := range v {
			iSum += v1
		}
		if iSum < iValue {
			iName = k
			iSlice = v
			iValue = iSum
		} else if iSum == iValue {
			for k2, v2 := range iSlice {
				if v2 < v[k2] {
					iName = k
					iSlice = v
					iValue = iSum
				}
			}
		}
	}
	return
}

// mulCardinality 普通索引通过离散度来进行筛选
func (cs TableInfoMeta) mulCardinality(mulSelection map[string] /*indexType*/ []string /*indexName*/, i global.IndexMerge) any {
	//先检查索引前导列的离散度（第一列离散度值，默认选择一个离散度最高的，如果离散度比较低，建议先analyze table一下）
	var cardinality uint64 = 0
	event := "[mulCardinality]"
	var indexName string
	if len(mulSelection) == 0 {
		return nil
	}
	for _, v := range mulSelection {
		for _, v1 := range v {
			var curryIndexCardinality uint64 = 0
			for _, v2 := range i.IndexColumn[v1] {
				if len(v2.Cardinality) > 0 {
					if parseUint, err2 := strconv.ParseUint(v2.Cardinality, 10, 64); err2 != nil {
						WLog.Error(fmt.Sprintf("(%d) %v string strconv uint64 fail. string is {%v}, error info is %v", 1, event, v2.Cardinality, err2))
					} else {
						if parseUint > curryIndexCardinality {
							curryIndexCardinality = parseUint
						}
					}
				}
			}
			if curryIndexCardinality >= cardinality {
				cardinality = curryIndexCardinality
				indexName = v1
			}
		}
	}
	return map[string]any{"indexName": indexName, "cardinality": cardinality}
}
func (cs TableInfoMeta) keyChoice(i global.IndexMerge) global.IndexSelection {
	//候选索引,先选择主键索引且索引列小于3，然后是唯一索引、普通索引
	var (
		candidateIndex                                             = map[string][]string{"pri": {}, "uni": {}, "mui": {}}
		cardinalityGather                                          []any
		maxCardinalityValue                                        uint64
		cardinalityMaxGather                                       = make(map[string]uint64)
		optimalIndexColumnType                                     []string
		multipleMaxCardinalityGather                               map[string] /*索引名*/ []int //类型定义值
		priSingleSelection, uniSingleSelection, muiSingleSelection = make(map[string][]string), make(map[string][]string), make(map[string][]string)
		priMullSelection, uniMullSelection, muiMullSelection       = make(map[string][]string), make(map[string][]string), make(map[string][]string)
	)
	for k, v := range i.IndexType {
		if strings.EqualFold(k, "_hidden_pk_") {
			continue
		}
		for _, v1 := range []string{"pri", "uni", "mul"} {
			if strings.EqualFold(v1, v) {
				if len(i.IndexColumn[k]) > 0 {
					candidateIndex[v1] = append(candidateIndex[v1], k)
				}
			}
		}
	}
	//没有索引则退出
	exit := true
	for _, v := range candidateIndex {
		if len(v) > 0 {
			exit = false
		}
	}
	if exit {
		return global.IndexSelection{}
	}
	//筛选主键或者唯一索引为单列的
	for k, v := range candidateIndex {
		var fp, fu, fm []string
		var mp, mu, mm []string
		for _, v1 := range v {
			if v2, ok := i.IndexColumn[v1]; ok {
				if len(v2) == 1 {
					if strings.EqualFold(k, "pri") {
						fp = append(fp, v1)
					} else if strings.EqualFold(k, "uni") {
						fu = append(fu, v1)
					} else if strings.EqualFold(k, "mul") {
						fm = append(fm, v1)
					}
				} else {
					if strings.EqualFold(k, "pri") {
						mp = append(mp, v1)
					} else if strings.EqualFold(k, "uni") {
						mu = append(mu, v1)
					} else if strings.EqualFold(k, "mul") {
						mm = append(mm, v1)
					}
				}
			}
		}
		if len(fp) > 0 {
			priSingleSelection[k] = fp
		}
		if len(fu) > 0 {
			uniSingleSelection[k] = fu
		}
		if len(fm) > 0 {
			muiSingleSelection[k] = fm
		}
		if len(mp) > 0 {
			priMullSelection[k] = mp
		}
		if len(mu) > 0 {
			uniMullSelection[k] = mu
		}
		if len(mm) > 0 {
			muiMullSelection[k] = mm
		}
	}
	cardinalityGather = append(cardinalityGather, cs.mulCardinality(priSingleSelection, i))
	cardinalityGather = append(cardinalityGather, cs.mulCardinality(priMullSelection, i))
	cardinalityGather = append(cardinalityGather, cs.mulCardinality(uniSingleSelection, i))
	cardinalityGather = append(cardinalityGather, cs.mulCardinality(uniMullSelection, i))
	cardinalityGather = append(cardinalityGather, cs.mulCardinality(muiSingleSelection, i))
	cardinalityGather = append(cardinalityGather, cs.mulCardinality(muiMullSelection, i))
	for _, v := range cardinalityGather {
		if v == nil {
			continue
		}
		curryCardinalityValue := v.(map[string]any)["cardinality"].(uint64)
		curryIndexName := fmt.Sprintf("%v", v.(map[string]any)["indexName"])
		if curryCardinalityValue > maxCardinalityValue {
			cardinalityMaxGather = map[string]uint64{
				curryIndexName: curryCardinalityValue,
			}
			maxCardinalityValue = curryCardinalityValue
		} else if curryCardinalityValue == maxCardinalityValue {
			cardinalityMaxGather[curryIndexName] = curryCardinalityValue
		}
	}
	//处理多个离散度相同的索引名
	multipleMaxCardinalityGather = make(map[string][]int)
	for k := range cardinalityMaxGather {
		if v1, ok := i.IndexColumn[k]; ok {
			multipleMaxCardinalityGather[k] = cs.typeSelection(v1)
		}
	}
	//按照数据类型规则找到最优的
	optimalIndexName := cs.typeSum(multipleMaxCardinalityGather)
	for _, v := range i.IndexColumn[optimalIndexName] {
		optimalIndexColumnType = append(optimalIndexColumnType, v.ColumnType)
	}
	return global.IndexSelection{
		IndexType:        i.IndexType[optimalIndexName],
		IndexColumn:      i.IndexColumn[optimalIndexName],
		IndexName:        optimalIndexName,
		IndexCardinality: cardinalityMaxGather[optimalIndexName],
		IndexColumnType:  optimalIndexColumnType,
	}
}

type columnSumGroupS struct {
	singleColumn   []string //索引名
	multipleColumn []string //索引名
}

// 根据索引列数分为单列索引和多列索引
func (cs TableInfoMeta) columnSumGroup(i global.IndexMerge) (q columnSumGroupS) {
	for _, indexName := range i.OrderByIndexName {
		if v, ok := i.IndexColumn[indexName]; ok {
			if len(v) == 1 {
				q.singleColumn = append(q.singleColumn, indexName)
			} else if len(v) > 1 {
				q.multipleColumn = append(q.multipleColumn, indexName)
			}
		}
	}
	return
}

type indexTypeGroupS struct {
	Primary   []string `json:"primary"`   //主键索引名
	UniqueKey []string `json:"uniqueKey"` //唯一索引名
	Key       []string `json:"key"`       //普通索引名
}

// 针对指定的索引名集合，做索引类型做聚合
func (cs TableInfoMeta) indexTypeGroup(indexGarth []string, i global.IndexMerge) (q indexTypeGroupS) {
	for _, v := range indexGarth {
		if key, ok := i.IndexType[v]; ok {
			switch key {
			case "pri":
				q.Primary = append(q.Primary, v)
			case "uni":
				q.UniqueKey = append(q.UniqueKey, v)
			case "mul":
				q.Key = append(q.Key, v)
			}
		}
	}
	return
}

type columnTypeGroupS struct {
	name       string
	class      string //类型
	null       string //null值约束
	dispersion string //离散度
}

// 针对指定的索引名集合，做索引类型做聚合
func (cs TableInfoMeta) columnTypeGroup(indexName string, i global.IndexMerge) (q []columnTypeGroupS) {
	if columnGarth, ok := i.IndexColumn[indexName]; ok {
		for _, v := range columnGarth {
			q = append(q, columnTypeGroupS{
				name:       v.ColumnName,
				class:      v.ColumnType,
				null:       v.Null,
				dispersion: v.Cardinality,
			})
		}
	}
	return
}
func (cs TableInfoMeta) columnTypePriority(q []columnTypeGroupS) (r []int) {
	if len(q) == 0 {
		return
	}
	for _, v := range q {
		switch strings.Split(strings.ToUpper(v.class), "(")[0] {
		case "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "BIGINT": //整数数值类型
			r = append(r, 1)
		case "FLOAT", "DOUBLE": //单精度和双精度的浮点数类型
			r = append(r, 1)
		case "DECIMAL": //定长数值类型
			r = append(r, 1)
		case "DATE", "TIME", "YEAR", "DATETIME", "TIMESTAMP": //时间类型
			r = append(r, 2)
		case "CHAR": //字符串定长
			r = append(r, 3)
		case "VARCHAR": //字符串变长
			r = append(r, 3)
		default:
			r = append(r, 1000)
		}
	}
	return
}
func (cs TableInfoMeta) columnNullGroup(q []columnTypeGroupS) (r []int) {
	for _, v := range q {
		if strings.EqualFold(v.null, "no") {
			r = append(r, 0)
		} else {
			r = append(r, 2)
		}
	}
	return
}
func (cs TableInfoMeta) columnDispersion(q []columnTypeGroupS) (r []int64) {
	for _, v := range q {
		if len(v.dispersion) == 0 {
			r = append(r, 0)
			continue
		}
		if t, err := strconv.ParseInt(v.dispersion, 10, 64); err != nil {
			r = append(r, 0)
		} else {
			r = append(r, t)
		}
	}
	return
}

func (cs TableInfoMeta) costChoice(i global.IndexMerge) (result global.IndexColumnSumGroup, err error) {
	var (
		indexTypeGarth map[string]any
	)
	type kv struct {
		key   string
		value int64
	}

	a := cs.columnSumGroup(i)
	for k1, indexGarth := range [][]string{
		a.singleColumn, a.multipleColumn,
	} {
		q := global.IndexPrioritySort{}
		//比较单、多列索引
		if indexTypeGarth, err = global.StructToMapAny(cs.indexTypeGroup(indexGarth, i)); err != nil {
			return
		}
		var pp = make(map[string]any)
		for k, v := range indexTypeGarth {
			var (
				indexNameGarth   []string
				sortedDispersion []kv
				dispersionPairs  []map[string]any
			)
			if v == nil {
				continue
			}
			switch fmt.Sprintf("%v", reflect.TypeOf(v)) {
			case "[]string":
				indexNameGarth = v.([]string)
			}
			if len(indexNameGarth) == 0 {
				continue
			}
			//按照列的离散度进行排序
			for _, indexName := range indexNameGarth {
				o := cs.columnDispersion(cs.columnTypeGroup(indexName, i))
				total := func() (sum int64) {
					for seq, dispersion := range o {
						if seq == len(o)-1 {
							sum = dispersion
						}
					}
					return
				}()
				sortedDispersion = append(sortedDispersion, kv{
					key:   indexName,
					value: total,
				})
			}
			sort.Slice(sortedDispersion, func(m, j int) bool {
				if sortedDispersion[m].value == sortedDispersion[j].value { //离散度相同
					//先比较数据类型优先级
					srcClassPriority := cs.columnTypePriority(cs.columnTypeGroup(sortedDispersion[m].key, i))
					srcClassTotal := func() (sum int) {
						for _, priorityValue := range srcClassPriority {
							sum += priorityValue
						}
						return
					}()

					dstClassPriority := cs.columnTypePriority(cs.columnTypeGroup(sortedDispersion[j].key, i))
					dstClassTotal := func() (sum int) {
						for _, priorityValue := range dstClassPriority {
							sum += priorityValue
						}
						return
					}()
					switch {
					case srcClassTotal > dstClassTotal:
						return sortedDispersion[m].value < sortedDispersion[j].value
					case srcClassTotal < dstClassTotal:
						return sortedDispersion[m].value > sortedDispersion[j].value
					}
					//再比较null值优先级
					srcNullPriority := cs.columnNullGroup(cs.columnTypeGroup(sortedDispersion[m].key, i))
					srcNullTotal := func() (sum int) {
						for _, priorityValue := range srcNullPriority {
							sum += priorityValue
						}
						return
					}()
					dstNullPriority := cs.columnNullGroup(cs.columnTypeGroup(sortedDispersion[j].key, i))
					dstNullTotal := func() (sum int) {
						for _, priorityValue := range dstNullPriority {
							sum += priorityValue
						}
						return
					}()
					switch {
					case srcNullTotal > dstNullTotal:
						return sortedDispersion[m].value < sortedDispersion[j].value
					case srcNullTotal < dstNullTotal:
						return sortedDispersion[m].value > sortedDispersion[j].value
					}
				}
				return sortedDispersion[m].value > sortedDispersion[j].value // 按 value 倒序
			})
			for _, subDispersion := range sortedDispersion {
				dispersionPairs = append(dispersionPairs, map[string]any{"name": subDispersion.key, "column": func() (m []string) {
					for _, col := range cs.columnTypeGroup(subDispersion.key, i) {
						m = append(m, col.name)
					}
					return
				}(), "cardinality": subDispersion.value})
			}
			pp[k] = dispersionPairs
		}
		var jsonData []byte
		if jsonData, err = json.Marshal(pp); err != nil {
			return
		}
		if err = json.Unmarshal(jsonData, &q); err != nil {
			return
		}
		switch k1 {
		case 0:
			result.Single = q
		case 1:
			result.Multiple = q
		}
	}
	return
}
func (cs TableInfoMeta) IndexChoice(s global.TablesMetaInfoInput) (result any, err error) {
	//var (
	//	Event = "[TableIndexChoice]"
	//)
	if len(s.IndexColumn) == 0 {
		return
	}
	//索引列处理，联合索引进行列合并
	//去除主键索引列、唯一索引列、普通索引列的所有列明
	f1 := cs.IndexMerge(s)
	if global.IsEmptyStruct(f1) {
		return
	}
	p := cs.keyChoice(f1)
	return p, nil
}

// IndexStatistics 获取MySQL表的索引统计信息
func (cs TableInfoMeta) IndexStatistics(s global.TablesMetaInfoInput) (any, error) {
	var (
		indexColumnData []global.IndexStatisticsS
		strSql          string
		event           = "[clusterIndexStatistics]"
		err             error
	)
	strSql = fmt.Sprintf("SELECT "+
		"table_schema as `databaseName` ,"+
		"table_name as `tableName`,"+
		"NON_UNIQUE as `uniqueType`,"+
		"INDEX_NAME as `indexName`,"+
		"SEQ_IN_INDEX as `columnSeq`,"+
		"COLUMN_NAME as `columnName`,"+
		"SUB_PART as `prefix`,"+
		"CARDINALITY as `cardinality` "+
		"FROM "+
		"information_schema.STATISTICS "+
		"where "+
		"table_schema in ('%v') and table_name in ('%v')"+
		"order by INDEX_NAME,SEQ_IN_INDEX", s.TableInfo.Schema, s.TableInfo.Table)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"]}
	var f1 any
	if _, f1, err = active.SqlQuery(strSql, ETL.SMap); err != nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,err:%v}", event, strSql, err))
		return nil, err
	}
	if fmt.Sprintf("%v", reflect.ValueOf(f1)) == "[]" {
		return nil, err
	}
	for _, v := range f1.([]map[string]interface{}) {
		var dd = global.IndexStatisticsS{}
		var jsonData []byte
		for g, j := range v {
			v[g] = fmt.Sprintf("%v", j)
		}
		jsonData, err = json.Marshal(v)
		if err != nil {
			err = errors.New(fmt.Sprintf("%v []map[string]any strconv json fail. Execution process{strconv data:%v error:%v}", event, v, err))
			return nil, err
		}
		// 反序列化JSON字符串到结构体
		err = json.Unmarshal(jsonData, &dd)
		if err != nil {
			err = errors.New(fmt.Sprintf("%v []byte json unmarshal struct fail. Execution process{strconv data:%v error:%v}", event, string(jsonData), err))
			return nil, err
		}
		indexColumnData = append(indexColumnData, dd)
	}
	return indexColumnData, err
}

/*
IndexColumn 查询MySQL库下指定表的索引统计信息
*/
func (cs TableInfoMeta) IndexColumn(s global.TablesMetaInfoInput) (any, error) {
	var (
		indexColumnData                []global.IndexColumn
		columnResult, statisticsResult any
		err                            error
		p                              TableColumn
		event                          = "[clusterIndexColumn]"
	)
	if columnResult, err = p.All(global.GetColumnTypeInput{DB: s.DB, TableInfo: s.TableInfo}); err != nil || columnResult == nil {
		err = errors.New(fmt.Sprintf("%v get Failed to obtain index information. error is:%v", event, err))
		return nil, err
	}
	if statisticsResult, err = cs.IndexStatistics(s); err != nil {
		err = errors.New(fmt.Sprintf("%v get Failed to obtain index information. error is:%v", event, err))
		return nil, err
	}
	if statisticsResult == nil {
		if s.TableInfo.HiddenIndex {
			indexColumnData = append(indexColumnData, global.IndexColumn{Schema: s.TableInfo.Schema, Table: s.TableInfo.Table,
				IndexName: "_hidden_pk_", ColumnType: "bigint", ColumnName: "_hidden_pk_", ColumnSeq: "1", ColumnKey: "U", Cardinality: "100",
				IndexCategory: "local"})
		} else {
			return nil, err
		}
	}
	for _, v := range statisticsResult.([]global.IndexStatisticsS) {
		var dd = global.IndexColumn{
			Schema:        v.Schema,
			Table:         v.Table,
			IndexName:     v.IndexName,
			ColumnName:    v.IndexColumn,
			IndexSeq:      v.IndexSeq,
			SubPart:       v.PreFix,
			Cardinality:   v.IndexCardinality,
			IndexCategory: "local",
		}
		dd.ColumnKey = func() string {
			switch {
			case strings.TrimSpace(v.UniqueType) == "0":
				switch strings.TrimSpace(v.IndexName) {
				case "PRIMARY":
					return "P"
				default:
					return "U"
				}
			default:
				return "M"
			}
		}()
		if dd.ColumnKey == "P" {
			dd.IndexName = "PRIMARY"
		}
		for _, k := range columnResult.(global.AllReturnResult).Result {
			if v.IndexColumn == k.ColumnName {
				dd.ColumnType = k.ColumnType
				dd.ColumnSeq = k.ColumnSeq
				dd.Null = k.IsNull
				break
			}
		}
		indexColumnData = append(indexColumnData, dd)
	}
	if s.TableInfo.HiddenIndex {
		indexColumnData = append(indexColumnData, global.IndexColumn{Schema: s.TableInfo.Schema, Table: s.TableInfo.Table,
			IndexName: "_hidden_pk_", ColumnType: "bigint", ColumnName: "_hidden_pk_", ColumnSeq: "1", ColumnKey: "U", Cardinality: "100",
			IndexCategory: "local"})
	}
	return indexColumnData, err
}
func (cs TableInfoMeta) DropIndexSql(s global.TablesMetaInfoInput) (any, error) {
	var (
		sqlS  string
		event = "[DropIndexSql]"
	)
	pc, _, _, _ := runtime.Caller(1)
	callingFunc := runtime.FuncForPC(pc)
	callFile, callLine := callingFunc.FileLine(pc)
	callFunc := callingFunc.Name()
	callFuncInfo := fmt.Sprintf("Call functions %v Call File %v:%v", callFunc, callFile, callLine)
	vlog = fmt.Sprintf("(%d) %v %v Start generating drop index repair statements for table %v.%v.", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table)
	WLog.Debug(vlog)
	if strings.EqualFold(s.IndexMetaGather.Dst.Name, "_hidden_pk_") {
		return global.AlterSqlResult{}, nil
	}
	switch s.IndexMetaGather.Dst.Type {
	case "pri":
		sqlS = fmt.Sprintf("alter table `%v`.`%v` drop primary key;", s.TableInfo.Schema, s.TableInfo.Table)
	case "uni", "mul":
		sqlS = fmt.Sprintf("alter table `%v.`%v drop index `%v`;", s.TableInfo.Schema, s.TableInfo.Table, s.IndexMetaGather.Dst.Name)
	}
	vlog = fmt.Sprintf("(%d) %v %v The drop index repair statement for table a is completed. result is {%v}", s.LogSeq, callFuncInfo, event, sqlS)
	WLog.Debug(vlog)
	return global.AlterSqlResult{
		Sql: sqlS,
	}, nil
}
func (cs TableInfoMeta) AddIndexSql(s global.TablesMetaInfoInput) (any, error) {
	var (
		sqlS             []string
		indexS           []string
		record, alterSql string
		event            = "[AddIndexSql]"
	)
	pc, _, _, _ := runtime.Caller(1)
	callingFunc := runtime.FuncForPC(pc)
	callFile, callLine := callingFunc.FileLine(pc)
	callFunc := callingFunc.Name()
	callFuncInfo := fmt.Sprintf("Call functions %v Call File %v:%v", callFunc, callFile, callLine)
	vlog = fmt.Sprintf("(%d) %v %v Start generating index repair statements for table %v.%v.", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table)
	WLog.Debug(vlog)
	if strings.EqualFold(s.IndexMetaGather.Dst.Name, "_hidden_pk_") {
		return global.AlterSqlResult{}, nil
	}
	for _, v := range s.IndexMetaGather.Src.IndexColumn {
		indexS = append(indexS, fmt.Sprintf("%v", v.ColumnName))
	}
	switch s.IndexMetaGather.Src.Type {
	case "pri":
		if len(s.IndexMetaGather.PartColumn) > 0 {
			indexS = append(indexS, s.IndexMetaGather.PartColumn...)
		}
		record = fmt.Sprintf("primary key must include partition column")
		sqlS = append(sqlS, fmt.Sprintf("alter table `%v`.`%v` add primary key(`%v`);", s.TableInfo.Schema, s.TableInfo.Table, strings.Join(indexS, "`,`")))
	case "uni":
		if len(s.IndexMetaGather.PartColumn) > 0 {
			indexS = append(indexS, s.IndexMetaGather.PartColumn...)
		}
		record = fmt.Sprintf("unique index must include partition column")
		sqlS = append(sqlS, fmt.Sprintf("alter table `%v`.`%v` add unique index %v(`%v`);", s.TableInfo.Schema, s.TableInfo.Table, s.IndexMetaGather.Src.Name, strings.Join(indexS, "`,`")))
	case "mul":
		sqlS = append(sqlS, fmt.Sprintf("alter table `%v`.`%v` add index %v(`%v`);", s.TableInfo.Schema, s.TableInfo.Table, s.IndexMetaGather.Src.Name, strings.Join(indexS, "`,`")))
	}
	alterSql = strings.Join(sqlS, ",")
	vlog = fmt.Sprintf("(%d) %v %v The index repair statement for table a is completed. result is {%v}", s.LogSeq, callFuncInfo, event, sqlS)
	WLog.Debug(vlog)
	return global.AlterSqlResult{
		Record: record,
		Sql:    alterSql,
	}, nil
}
func (cs TableInfoMeta) ModifyIndexSql(s global.TablesMetaInfoInput) (any, error) {
	var (
		sqlS             []string
		indexS, triggerS []string
		event            = "[ModifyIndexSql]"
		alterSql, record string
		normal           bool
	)
	pc, _, _, _ := runtime.Caller(1)
	callingFunc := runtime.FuncForPC(pc)
	callFile, callLine := callingFunc.FileLine(pc)
	callFunc := callingFunc.Name()
	callFuncInfo := fmt.Sprintf("Call functions %v Call File %v:%v", callFunc, callFile, callLine)
	vlog = fmt.Sprintf("(%d) %v %v Start generating modify index repair statements for table %v.%v.", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table)
	WLog.Debug(vlog)
	if strings.EqualFold(s.IndexMetaGather.Dst.Name, "_hidden_pk_") {
		return global.AlterSqlResult{}, nil
	}
	for _, v := range s.IndexMetaGather.Src.IndexColumn {
		indexS = append(indexS, fmt.Sprintf("%v", v.ColumnName))
	}
	for _, v := range s.IndexMetaGather.Dst.IndexColumn {
		triggerS = append(triggerS, fmt.Sprintf("%v", v.ColumnName))
	}
	//处理drop index
	switch s.IndexMetaGather.Dst.Type {
	case "pri":
		sqlS = append(sqlS, fmt.Sprintf("alter table `%v`.`%v` drop primary key", s.TableInfo.Schema, s.TableInfo.Table))
	case "uni", "mul":
		sqlS = append(sqlS, fmt.Sprintf("alter table `%v`.`%v` drop index `%v`", s.TableInfo.Schema, s.TableInfo.Table, s.IndexMetaGather.Dst.Name))
	}
	switch s.IndexMetaGather.Src.Type {
	case "pri":
		sqlS = append(sqlS, fmt.Sprintf(" add primary key(`%v`)", strings.Join(indexS, "`,`")))
		a, b := ea.CheckSum().Arrcmp(append(indexS, s.IndexMetaGather.PartColumn...), triggerS)
		if len(a) == 0 && len(b) == 0 {
			normal = true
			record = fmt.Sprintf("primary key must include partition column")
		}
	case "uni":
		sqlS = append(sqlS, fmt.Sprintf(" add unique index `%v`(`%v`)", s.IndexMetaGather.Src.Name, strings.Join(indexS, "`,`")))
		a, b := ea.CheckSum().Arrcmp(append(indexS, s.IndexMetaGather.PartColumn...), triggerS)
		if len(a) == 0 && len(b) == 0 {
			normal = true
			record = fmt.Sprintf("unique index must include partition column")
		}
	case "mul":
		sqlS = append(sqlS, fmt.Sprintf(" add index `%v`(`%v`)", s.IndexMetaGather.Src.Name, strings.Join(indexS, "`,`")))
	}
	vlog = fmt.Sprintf("(%d) %v %v The modify index repair statement for table a is completed. result is {%v}", s.LogSeq, callFuncInfo, event, sqlS)
	WLog.Debug(vlog)
	if !normal {
		alterSql = strings.Join(sqlS, ",")
	}
	return global.AlterSqlResult{
		Record: record,
		Sql:    alterSql,
	}, nil
}
